package com.ray.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hp.hpl.sparta.xpath.ThisNodeTest;



/**
 * List<T> 输出到Excel
 * @author Nate_Lu
 *
 */
public class ExcelExportUtil {
	
	public ExcelExportUtil() {
		super();
	}
	/**
	 * List<T> to ExcelFile 把T类型的所有字段和值输出excel到指定文件夹,或把T类型指定字段输出Excel到指定文件夹
	 * @param list <T>
	 * @param outFoderPath 输出文件夹
	 * @param fields	可选字段
	 * @return 输出的xlsx文件
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	public static String getValueFromObject(Object o,String field) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException{
		//去掉一層 user.name-> name
		if(null==o)return"";
		String[] fieldGrade=field.split("\\.");
		Object value=null;
		if(o instanceof Map){ 	
			value = ((Map) o).get(fieldGrade[0]);
		}
		if(o instanceof List){ 	
			int i = Integer.parseInt(fieldGrade[0]);
			value = ((List) o).get(i);
		}
		if(value==null){ 	
			//反射获取
			try {
				Field fieldRefect = o.getClass().getDeclaredField(fieldGrade[0]);
				fieldRefect.setAccessible(true);
				value = fieldRefect.get(o);
			} catch (Exception e) {
				return null;
			}
		}
		//如果是最後一層，返回
		if(fieldGrade.length-1<=0){
			if(value==null) {
				value = "";
			}
			return value.toString();
		}else{
			return getValueFromObject(value,field.substring(fieldGrade[0].length()+1));
		}
		
	}
	/**
	 * 根據字段數組獲取map、list、object的值  如map.key list.1 object.name
	 * @param o
	 * @param fields
	 * @return
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public static List<String> getLineFromObject(Object o,String... fields) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException{
		
			List<String> line=new ArrayList<String>();
				for (String filed : fields) {
					String string = getValueFromObject(o,filed);
					line.add(string);
				}
				return line;
	}
	
	public static <T> File createExcelFile(List<T> list,String outFoderPath,String [] heads, String... fields) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
		File outFile= new File(outFoderPath, UUID.randomUUID().toString() + ".xlsx");
		if(!outFile.getParentFile().exists()){
			outFile.getParentFile().mkdirs();
		}
		Workbook wb;
		OutputStream fos = null;
		
//		int colIndex = 0;
//		Cell cell;
		try {
			wb = new XSSFWorkbook();
			Sheet sheet = wb.createSheet();
			int rowIndex = 0;
			int colIndex=0;
			Row row;
			Cell cell;
			row=sheet.createRow(rowIndex);
			for(String ss:heads){
				cell = row.createCell(colIndex);
				cell.setCellValue(ss);
				colIndex++;
			}
			rowIndex++;
			for (Object o : list) {
				row=sheet.createRow(rowIndex);
				List<String> line=getLineFromObject(o,fields);
				colIndex = 0;
				for(String sss:line){
					cell = row.createCell(colIndex);
					cell.setCellValue(sss);
					colIndex++;
				}
//				line.forEach(rowWrite->{
//					Cell cell = row.createCell(colIndex);
//					cell.setCellValue(rowWrite);
//					colIndex++;
//				});
				rowIndex++;
			}

			fos = new FileOutputStream(outFile);
			wb.write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != fos)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return outFile;
	}
	
	/**
	 * 导出excel文件带样式
	 * @param list 表数据
	 * @param outFoderPath 输出文件夹路径
	 * @param heads 表头字段
	 * @param fields 内容字段
	 * @param sheetName 工作表名称
	 * @return
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * @author 王荣
	 */
	public static <T> File createExcelFileWithStyle(List<T> list,String outFoderPath,String [] heads, String[] fields,String sheetName) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
		File outFile= new File(outFoderPath, UUID.randomUUID().toString() + ".xlsx");
		if(!outFile.getParentFile().exists()){
			outFile.getParentFile().mkdirs();
		}
		Workbook wb;
		OutputStream fos = null;
		try {
			wb = new XSSFWorkbook();
			Sheet sheet = wb.createSheet(sheetName);
			sheet.createFreezePane(1, 1, 1, 1);//冻结第一列、第一行
			int rowIndex = 0;
			int colIndex=0;
			Row row;
			Cell cell;
			row=sheet.createRow(rowIndex);
			row.setHeightInPoints(20);
			//表格表头样式
			CellStyle headerStyle = ExcelExportUtil.getExcelHeaderStyle(wb);
	        //表格内容样式
			CellStyle bodyStyle = ExcelExportUtil.getExcelBodyStyle(wb);
	        
			int[] byteLengthArr = new int[heads.length];
			for(String ss:heads){
				cell = row.createCell(colIndex);
				cell.setCellValue(ss);
				cell.setCellStyle(headerStyle);
				byteLengthArr[colIndex] = ss.getBytes().length*2;//默认表头都是中文
				colIndex++;
			}
			rowIndex++;
			for (Object o : list) {
				row=sheet.createRow(rowIndex);
				List<String> line=getLineFromObject(o,fields);
				colIndex = 0;
				int fontRowCount = 1;
				for(String cellContent:line){
					cellContent = cellContent == null?"":cellContent;
					cell = row.createCell(colIndex);
					cell.setCellValue(cellContent);
					cell.setCellStyle(bodyStyle);
					
					if(cellContent.contains("\n\r")) {
						String[] tStrings = cellContent.split("\n\r");
						for(String tString : tStrings) {
							int length = StringUtil.isContainChinese(tString)?tString.getBytes().length*2:tString.getBytes().length;
							if(byteLengthArr[colIndex]<length) {
								byteLengthArr[colIndex] = length;
							}
						}
						fontRowCount = tStrings.length>fontRowCount?tStrings.length:fontRowCount;
					}else {
						int length = StringUtil.isContainChinese(cellContent)?cellContent.getBytes().length*2:cellContent.getBytes().length;
						if(byteLengthArr[colIndex]<length) {
							byteLengthArr[colIndex] = length;
						}
					}
					colIndex++;
				}
				row.setHeightInPoints(15*fontRowCount);//调整行高
				rowIndex++;
			}
			//调整列宽
			for(int i=0;i<heads.length;i++) {
				sheet.setColumnWidth(i, byteLengthArr[i]>40?41*256:(1+byteLengthArr[i])*256);
			}
			fos = new FileOutputStream(outFile);
			wb.write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != fos)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return outFile;
	}
	
	/*public static <T> File createExcelFile0(List<T> list,String outFoderPath, String... fields) {
		File outFile= new File(outFoderPath, UUID.randomUUID().toString() + ".xlsx");
		if(!outFile.getParentFile().exists()){
			outFile.getParentFile().mkdirs();
		}
		Workbook wb;
		OutputStream fos = null;
		try {
			wb = new XSSFWorkbook();
			Sheet sheet = wb.createSheet();
			if(list!=null&&list.size()>0){
				if(list.get(0)instanceof JSONObject){
					writeTestRecordToSheetForJsonObjList((List<JSONObject>) list, sheet, fields);
				}else{
					writeTestRecordToSheet(list, sheet, fields);
				}
			}else{
				System.out.println("\tdebug: 目標list為null,或size為0");
			}

			fos = new FileOutputStream(outFile);
			wb.write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			System.out.println("使用反射获取对象属性值异常");
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			System.out.println("使用反射获取对象属性值异常");
			e.printStackTrace();
		} finally {
			try {
				if (null != fos)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return outFile;
	}*/
	/**
	 * lsit<JSONObj> to sheet
	 * @param list
	 * @param sheet
	 * @param fields
	 */
	/*private static void writeTestRecordToSheetForJsonObjList(List<JSONObject> list, Sheet sheet, String... stringFields) {
		if (null == list || list.size() <= 0)
			return;
		//獲取表頭fields
		JSONObject o=list.get(0);
			Set set = o.keySet();
			String[] fields=new String[o.size()];
			int index=0;
			for (Object key : set) {
				//System.out.println(key);
				fields[index++]=key.toString();
			}
		int rowIndex = 1;
		int colIndex = 0;
		Row row;
		Cell cell;
		if (null == stringFields || stringFields.length <= 0) {
			// 产生表头
			row = sheet.createRow(0);
			for (String field : fields) {
				cell = row.createCell(colIndex++);
				cell.setCellValue(field);
			}
			// 产生表体
			for (JSONObject e : list) {
				row = sheet.createRow(rowIndex);
				colIndex = 0;
				for (String field : fields) {
					cell = row.createCell(colIndex);
					cell.setCellValue(e.get(field)==null?"":e.get(field).toString());
					colIndex++;
				}
				rowIndex++;
			}
		} else {
						// 产生表头
						row = sheet.createRow(0);
						for (String field : stringFields) {
							cell = row.createCell(colIndex++);
							cell.setCellValue(field);
						}
						// 产生表体
						for (JSONObject e : list) {
							row = sheet.createRow(rowIndex);
							colIndex = 0;
							for (String field : stringFields) {
								cell = row.createCell(colIndex);
								cell.setCellValue(e.get(field)==null?"":e.get(field).toString());
								colIndex++;
							}
							rowIndex++;
						}
		}
	}*/

	/**
	 * list<T> to sheet
	 * @param list 
	 * @param sheet
	 * @param stringFields 可选字段，空则转换所有字段
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	private static <T> void writeTestRecordToSheet(List<T> list, Sheet sheet, String... stringFields)
			throws IllegalArgumentException, IllegalAccessException {
		if (null == list || list.size() <= 0)
			return;

		int rowIndex = 1;
		int colIndex = 0;
		Row row;
		Cell cell;
		if (null == stringFields || stringFields.length <= 0) {
			// 产生表头
			Field[] fields = list.get(0).getClass().getDeclaredFields();
			row = sheet.createRow(0);
			for (Field field : fields) {
				cell = row.createCell(colIndex++);
				cell.setCellValue(field.getName());
			}
			// 产生表体
			for (T e : list) {
				Field[] fileds = e.getClass().getDeclaredFields();
				row = sheet.createRow(rowIndex);
				colIndex = 0;
				for (Field field : fileds) {
					field.setAccessible(true);
					cell = row.createCell(colIndex);
					cell.setCellValue(field.get(e)==null?"NA":field.get(e).toString());
					colIndex++;
				}
				rowIndex++;
			}
		} else {
			// 产生表头
			List<String> asList = Arrays.asList(stringFields);
			Field[] fields = list.get(0).getClass().getDeclaredFields();
			row = sheet.createRow(0);
			for (Field field : fields) {
				if (asList.contains(field.getName())) {
					cell = row.createCell(colIndex++);
					cell.setCellValue(field.getName());
				}
			}
			// 产生表体
			for (T e : list) {
				Field[] fileds = e.getClass().getDeclaredFields();
				row = sheet.createRow(rowIndex);
				colIndex = 0;
				for (Field field : fileds) {
					if (asList.contains(field.getName())) {
						field.setAccessible(true);
						cell = row.createCell(colIndex);
						cell.setCellValue(field.get(e)==null?"":field.get(e).toString());
						colIndex++;
					}
				}
				rowIndex++;
			}
		}
	}
	/**
	 * 表头样式
	 * @param workbook
	 * @return CellStyle
	 * @author 王荣
	 */
	public static CellStyle getExcelHeaderStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		//设置字体样式
		Font font = workbook.createFont();
		font.setFontName("新宋体");
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 12);
		style.setFont(font);
        //设置边框类型
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置单元格的对齐类型
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
		return style;
	}
	/**
	 * 表格内容样式
	 * @param workbook
	 * @return CellStyle
	 * @author 王荣
	 */
	public static CellStyle getExcelBodyStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		//设置可换行
		style.setWrapText(true);
		//设置字体样式
		Font font = workbook.createFont();
		font.setFontName("黑体");
		style.setFont(font);
        //设置边框类型
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置单元格的对齐类型
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
		return style;
	}
}
